package com.dfrz.bookstore.dao.impl;

import com.dfrz.bookstore.dao.Dao;
import com.dfrz.bookstore.entry.Book;
import com.dfrz.bookstore.entry.User;
import com.dfrz.bookstore.util.JdbcUtils;

import javax.xml.crypto.Data;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 类功能描述:
 *   用户的增删改查 登录
 * @Author keyan
 * @Date 2021/5/21 20:18
 */
public class UserDaoImpl implements Dao<User> {

    public User loginByEmail(User user){
        Connection conn = null;
        ResultSet resultSet = null;
        PreparedStatement prep = null;
        String sql = "select * from user where email = ? ";
        try {
            conn =  JdbcUtils.getConnection();
            prep = conn.prepareStatement(sql);
            prep.setString(1,user.getEmail());
            resultSet = prep.executeQuery();
            resultSet.next();
            User user1 = new User();
            user1.setId(resultSet.getInt("id"));
            user1.setLoginName(resultSet.getString("login_name"));
            user1.setPassWord(resultSet.getString("password"));
            user1.setName(resultSet.getString("name"));
            user1.setSex(resultSet.getString("sex"));
            user1.setEmail(resultSet.getString("email"));
            user1.setTel(resultSet.getString("tel"));
            user1.setLevel(resultSet.getInt("level"));
            user1.setAddress(resultSet.getString("address"));
            user1.setUserImage(resultSet.getString("user_image"));
            return user1;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,prep,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    public User login(User user){

        Connection conn = null;
        ResultSet resultSet = null;
        PreparedStatement prep = null;
        String sql = "select * from user where login_name = ? and password = ? ";
        try {
            conn =  JdbcUtils.getConnection();
            prep = conn.prepareStatement(sql);
            prep.setString(1,user.getLoginName());
            prep.setString(2,user.getPassWord());
            resultSet = prep.executeQuery();
            if (!resultSet.next()){
                return null;
            }
            User user1 = new User();
            user1.setId(resultSet.getInt("id"));
            user1.setLoginName(resultSet.getString("login_name"));
            user1.setPassWord(resultSet.getString("password"));
            user1.setName(resultSet.getString("name"));
            user1.setUserImage(resultSet.getString("user_image"));
            user1.setSex(resultSet.getString("sex"));
            user1.setEmail(resultSet.getString("email"));
            user1.setTel(resultSet.getString("tel"));
            user1.setLevel(resultSet.getInt("level"));
            user1.setAddress(resultSet.getString("address"));
            return user1;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,prep,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    @Override
    public boolean insert(User user) {
        String sql = "insert into `user` (login_name,password,name,sex,email,tel,level,address,user_image) values (?,?,?,?,?,?,?,?,?)";
        PreparedStatement prep = null;
        Connection conn = null;
        try {
            conn = JdbcUtils.getConnection();
            prep = conn.prepareStatement(sql);
            prep.setString(1,user.getLoginName());
            prep.setString(2,user.getPassWord());
            prep.setString(3,user.getName());
            prep.setString(4,user.getSex());
            prep.setString(5,user.getEmail());
            prep.setString(6,user.getTel());
            if (user.getLevel()==0){
               user.setLevel(1);  //设置默认等级1
            }
            prep.setInt(7,user.getLevel());
            prep.setString(8,user.getAddress());
            if (user.getUserImage()==null){
                user.setUserImage("https://keyantong.oss-cn-hangzhou.aliyuncs.com/user_image.jpeg");//设置默认头像
            }
            prep.setString(9,user.getUserImage());
            int i = prep.executeUpdate();
            if (i>0){
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,prep,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public boolean deleteObjById(int i) {
        String sql = "delete from user where id = "+i ;
        Connection conn ;
        Statement stat;
        try {
            conn = JdbcUtils.getConnection();
            stat = conn.createStatement();
            int i1 = stat.executeUpdate(sql);
            if (i1>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean updateByObj(User user) {
        if (user.getId()==0){
            return false;
        }
        StringBuilder sb = new StringBuilder("update user set id = "+user.getId());
        if (user.getLoginName()!=null){
            sb.append(" , login_name = '"+user.getLoginName()+"' ");
        }
        if (user.getPassWord()!=null){
            sb.append(" , password = '"+user.getPassWord()+"' ");
        }
        if (user.getName()!=null){
            sb.append(" , name = '"+user.getName()+"' ");
        }
        if (user.getSex()!=null){
            sb.append(" , sex = '"+user.getSex()+"' ");
        }
        if (user.getEmail()!=null){
            sb.append(" , email = '" +user.getEmail()+"' ");
        }
        if (user.getTel()!=null){
            sb.append(" , tel = '"+user.getTel()+"' ");
        }
        if (user.getLevel()!=0){
            sb.append(" , level = "+user.getLevel());
        }
        if (user.getAddress()!=null){
            sb.append(" , address = ' "+user.getAddress()+" '");
        }
        if (user.getUserImage()!=null){
            sb.append(" , user_image = '"+user.getUserImage()+" '");
        }
        sb.append(" where id = "+user.getId());
        Connection conn = null;
        Statement stat = null;

        try {
            conn = JdbcUtils.getConnection();
            stat =  conn.createStatement();
            int i = stat.executeUpdate(sb.toString());
            if (i>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,stat,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public List<User> findObjByObj(User user) {
        List<User> users = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select * from user where 1=1 ");
        if (user.getId()!= 0){
            sb.append(" and id = "+user.getId());
        }
        if (user.getLoginName()!=null){
            sb.append(" and login_name like '%"+user.getLoginName()+"%' ");
        }
        if (user.getName()!=null){
            sb.append(" and name like '%"+user.getName()+"%' ");
        }
        if (user.getSex()!=null){
            sb.append(" and sex like '%"+user.getSex()+"%' ");
        }
        if (user.getEmail()!=null){
            sb.append(" and email like '%"+user.getEmail()+"%' ");
        }
        if (user.getTel()!=null){
            sb.append(" and tel like '%"+user.getTel()+"%' ");
        }
        if (user.getLevel()!=0){
            sb.append(" and level = '"+user.getLevel()+"' ");  //有问题
        }
        if (user.getAddress()!=null){
            sb.append(" and address like '%"+user.getAddress()+"%' ");
        }
        Connection conn = null;
        Statement stat = null;
        ResultSet resultSet =null;
        try {
            conn= JdbcUtils.getConnection();
            stat = conn.createStatement();
            resultSet = stat.executeQuery(sb.toString());

            while (resultSet.next()){
                User user1 = new User();
                user1.setId(resultSet.getInt("id"));
                user1.setLoginName(resultSet.getString("login_name"));
                user1.setPassWord(resultSet.getString("password"));
                user1.setName(resultSet.getString("name"));
                user1.setSex(resultSet.getString("sex"));
                user1.setEmail(resultSet.getString("email"));
                user1.setTel(resultSet.getString("tel"));
                user1.setLevel(resultSet.getInt("level"));
                user1.setAddress(resultSet.getString("address"));
                user1.setCreate_time(resultSet.getDate("create_date"));
                user1.setUserImage(resultSet.getString("user_image"));
                users.add(user1);
            }
            /*返回查找到的用户*/
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,stat,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public List<User> findObjByPage(User user, int pageNum, int pageSize) {
        int start = (pageNum-1)*pageSize;
        List<User> users = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select * from user where 1 = 1 ");
        if (user.getId()!= 0){
            sb.append(" and id = "+user.getId());
        }
        if (user.getLoginName()!=null){
            sb.append(" and login_name like '%"+user.getLoginName()+"%' ");
        }
        if (user.getName()!=null){
            sb.append(" and name like '%"+user.getName()+"%' ");
        }
        if (user.getSex()!=null){
            sb.append(" and sex like '%"+user.getSex()+"%' ");
        }
        if (user.getEmail()!=null){
            sb.append(" and `email` like '%"+user.getEmail()+"%' ");
        }
        if (user.getTel()!=null){
            sb.append(" and tel like '%"+user.getTel()+"%' ");
        }
        if (user.getLevel()!=0){
            sb.append(" and level = '"+user.getLevel()+"' ");  //有问题
        }
        if (user.getAddress()!=null){
            sb.append(" and address like '%"+user.getAddress()+"%' ");
        }
        sb.append(" limit "+ start +","+pageSize);
        Connection conn = null;
        Statement stat = null;
        ResultSet resultSet =null;
        try {
            conn= JdbcUtils.getConnection();
            stat = conn.createStatement();
            resultSet = stat.executeQuery(sb.toString());

            while (resultSet.next()){
                User user1 = new User();
                user1.setId(resultSet.getInt("id"));
                user1.setLoginName(resultSet.getString("login_name"));
                user1.setPassWord(resultSet.getString("password"));
                user1.setName(resultSet.getString("name"));
                user1.setSex(resultSet.getString("sex"));
                user1.setEmail(resultSet.getString("email"));
                user1.setTel(resultSet.getString("tel"));
                user1.setLevel(resultSet.getInt("level"));
                user1.setAddress(resultSet.getString("address"));
                users.add(user1);
            }
            /*返回查找到的用户*/
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.close(conn,stat,resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}
